"""
Case Type   : 系统管理函数-恢复控制函数（备机执行）
Case Name   : 函数pg_last_xact_replay_timestamp()，获取最后一个事务在恢复时重放的时间戳
Create At   : 2020/8/2
Owner       : @peilinqian
Description :
    1.主机新建测试表
    2.未进行任何事物，备机执行函数pg_last_xact_replay_timestamp()获取初始最新回放时间戳
    3.主机创建事务并rollback
    4.主备同步后，备机执行函数pg_last_xact_replay_timestamp()获取当前最新回放时间戳
    5.主机创建事务并commit
    6.主备同步后，备机执行函数pg_last_xact_replay_timestamp()获取当前最新回放时间戳
Expect      :
    1.成功
    2.成功
    3.成功
    4.查询结果大于步骤2
    5.成功
    6.查询结果大于步骤4
History     : 
"""

import os
import unittest
from datetime import datetime

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

pri_sh = CommonSH('PrimaryDbUser')
node_num = pri_sh.get_node_num()


@unittest.skipIf(node_num < 3, '非1主2备环境不执行')
class Tools(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.sta1_sh = CommonSH('Standby1DbUser')
        self.constant = Constant()
        self.tb_name = 't_innerfunc_system_manage_case0020'

    def test_func_sys_manage(self):
        text = '-----step1:主机新建测试表-----'
        self.log.info(text)
        sql = f"drop table if exists {self.tb_name};" \
              f"create table {self.tb_name} (sk integer);" \
              f"insert into {self.tb_name} values(1);"
        res = pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, res, '执行失败' + text)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, res, '执行失败' + text)

        text = '----step2:未进行任何事物，' \
               '备机执行函数pg_last_xact_replay_timestamp()获取初始最新回放时间戳----'
        self.log.info(text)
        check_sql = 'select pg_last_xact_replay_timestamp();'
        check_result = self.sta1_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        timestr = check_result.splitlines()[-2].strip()[0:-3]
        timestamp1 = datetime.strptime(timestr, '%Y-%m-%d %H:%M:%S.%f')
        self.assertIsNotNone(timestamp1, '执行失败' + text)

        text = '----step3:主机创建事务并rollback-----'
        self.log.info(text)
        xact_sql = f'start transaction;' \
                   f'insert into {self.tb_name} values(2);' \
                   f'select * from {self.tb_name};' \
                   f'rollback;' \
                   f'select * from {self.tb_name};'
        xact_result = pri_sh.execut_db_sql(xact_sql)
        self.log.info(xact_result)
        self.assertIn('ROLLBACK', xact_result, '执行失败' + text)

        text = '----step4:主备同步后，' \
               '备机执行函数pg_last_xact_replay_timestamp()获取当前最新回放时间戳----'
        self.log.info(text)
        self.log.info('----等待主备同步----')
        self.sta1_sh.check_location_consistency('standby', node_num)
        check_sql = 'select pg_last_xact_replay_timestamp();'
        check_result = self.sta1_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        timestr = check_result.splitlines()[-2].strip()[0:-3]
        timestamp2 = datetime.strptime(timestr, '%Y-%m-%d %H:%M:%S.%f')
        self.assertGreater(timestamp2, timestamp1, '执行失败' + text)

        text = '----step5:主机创建事务并commit----'
        self.log.info(text)
        xact_sql = f'start transaction;' \
                   f'insert into {self.tb_name} values(2);' \
                   f'select * from {self.tb_name};' \
                   f'commit;'
        xact_result = pri_sh.execut_db_sql(xact_sql)
        self.log.info(xact_result)
        self.assertIn('COMMIT', xact_result, '执行失败' + text)

        text = '----step6:主备同步后，' \
               '备机执行函数pg_last_xact_replay_timestamp()获取当前最新回放时间戳----'
        self.log.info(text)
        self.log.info('----等待主备同步----')
        self.sta1_sh.check_location_consistency('standby', node_num)
        check_sql = 'select pg_last_xact_replay_timestamp();'
        check_result = self.sta1_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        timestr = check_result.splitlines()[-2].strip()[0:-3]
        timestamp3 = datetime.strptime(timestr, '%Y-%m-%d %H:%M:%S.%f')
        self.assertGreater(timestamp3, timestamp2, '执行失败' + text)

    def tearDown(self):
        self.log.info('-----清理环境------')
        sql = f'drop table if exists {self.tb_name};'
        res = pri_sh.execut_db_sql(sql)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, res)
        self.log.info(f'-----{os.path.basename(__file__)} start-----')